package io.renren.common.utils;

import io.renren.modules.bs.vo.BsDepartmentPatientPageVo;
import io.renren.modules.bs.vo.ExportExtraRecordExcelVo;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.web.context.request.RequestContextHolder;
import org.springframework.web.context.request.ServletRequestAttributes;

import javax.servlet.http.HttpServletRequest;
import java.awt.image.BufferedImage;
import java.text.SimpleDateFormat;
import java.util.List;

public class ExcelUtil {

    private static Logger logger = LoggerFactory.getLogger(ExcelUtil.class);

    /**
     * 导出Excel（样品）
     *
     * @param sheetName sheet名称
     * @param title     标题
     * @param wb        HSSFWorkbook对象
     * @return
     */
    public static HSSFWorkbook getHSSFWorkbook(String sheetName, String[] title, List<ExportExtraRecordExcelVo> list, HSSFWorkbook wb) {
        HttpServletRequest request = ((ServletRequestAttributes) RequestContextHolder.getRequestAttributes()).getRequest();
        // 第一步，创建一个HSSFWorkbook，对应一个Excel文件
        if (wb == null) {
            wb = new HSSFWorkbook();
        }

        // 第二步，在workbook中添加一个sheet,对应Excel文件中的sheet
        HSSFSheet sheet = wb.createSheet(sheetName);

        // 第三步，在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制

        //声明列对象
        HSSFCell cell = null;
        HSSFRow row = null;

        int totalCount = 0;
        for (ExportExtraRecordExcelVo exportExtraRecordExcelVo:list) {
            totalCount += exportExtraRecordExcelVo.getSubExtraRecordExcelVos().size();
        }
        for (int i = 0; i < totalCount+1; i++) {
            row = sheet.createRow(i);
            for (int j = 0; j < title.length; j++) {
                row.createCell(j);
            }
        }
        row = sheet.getRow(0);
        row.setHeight((short) 650);
        // 第四步，创建单元格，并设置值表头 设置表头居中
        HSSFCellStyle style = wb.createCellStyle();
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 创建一个居中格式
        style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);


        //创建标题
        for (int i = 0; i < title.length; i++) {
            sheet.setColumnWidth(i, 6000);
            cell = row.getCell(i);
            cell.setCellValue(title[i]);
            HSSFFont font = wb.createFont();
            font.setFontName("黑体");
            font.setFontHeightInPoints((short) 15);//设置字体大小
            style.setFont(font);
            cell.setCellStyle(style);
        }
        BufferedImage bufferImg = null;//图片一
        BufferedImage bufferImg1 = null;//图片二
        try {
            //创建内容
            HSSFCellStyle styleCon = wb.createCellStyle();
            styleCon.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 创建一个居中格式
            styleCon.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
            int raw = 1;

            for (int i = 0; i < list.size(); i++) {
                ExportExtraRecordExcelVo exportExtraRecordExcelVo = list.get(i);
                int size = exportExtraRecordExcelVo.getSubExtraRecordExcelVos().size();

                row = sheet.getRow(raw);
                row.setHeight((short) 550);
                cell = row.getCell(0);
                cell.setCellValue(exportExtraRecordExcelVo.getHospitalName());
                cell.setCellStyle(styleCon);
                cell = row.getCell(1);
                cell.setCellValue(exportExtraRecordExcelVo.getDepartmentName());
                cell.setCellStyle(styleCon);
                cell = row.getCell(2);
                cell.setCellValue(exportExtraRecordExcelVo.getExtractNum());
                cell.setCellStyle(styleCon);
                cell = row.getCell(3);
                cell.setCellValue(exportExtraRecordExcelVo.getIsDoneValue());
                cell.setCellStyle(styleCon);
                CellRangeAddress region0 = new CellRangeAddress(raw, raw + size - 1, 0, 0);
                CellRangeAddress region1 = new CellRangeAddress(raw, raw + size - 1, 1, 1);
                CellRangeAddress region2 = new CellRangeAddress(raw, raw + size - 1, 2, 2);
                CellRangeAddress region3 = new CellRangeAddress(raw, raw + size - 1, 3, 3);
                sheet.addMergedRegion(region0);
                sheet.addMergedRegion(region1);
                sheet.addMergedRegion(region2);
                sheet.addMergedRegion(region3);
                int m = 0;
                raw = raw + 1;
                for (ExportExtraRecordExcelVo patientEntity : exportExtraRecordExcelVo.getSubExtraRecordExcelVos()) {
                    if(m > 0){
                    row = sheet.getRow(raw);
                    row.setHeight((short) 550);
                    raw = raw + 1;
                    }
                    cell = row.getCell(4);
                    cell.setCellValue(patientEntity.getPatientName());
                    cell.setCellStyle(styleCon);
                    Integer gender = patientEntity.getGender();
                    String genderVal = "";
                    if(gender != null){
                        if (gender == 1) {
                            genderVal = "男";
                        } else {
                            genderVal = "女";
                        }
                    }

                    cell = row.getCell(5);
                    cell.setCellValue(genderVal);
                    cell.setCellStyle(styleCon);
                    cell = row.getCell(6);
                    cell.setCellValue(patientEntity.getIdNumber());
                    cell.setCellStyle(styleCon);
                    m++;
                }

            }
            return wb;
        } catch (Exception e) {
            e.printStackTrace();
            // TODO: handle exception
            System.err.println(e.getMessage());
        }
        return wb;
    }



    /**
     * 导出Excel（样品）
     *
     * @param sheetName sheet名称
     * @param title     标题
     * @param wb        HSSFWorkbook对象
     * @return
     */
    public static HSSFWorkbook getHSSFWorkbook1(String sheetName, String[] title, List<BsDepartmentPatientPageVo> list, HSSFWorkbook wb) {
        HttpServletRequest request = ((ServletRequestAttributes) RequestContextHolder.getRequestAttributes()).getRequest();
        // 第一步，创建一个HSSFWorkbook，对应一个Excel文件
        if (wb == null) {
            wb = new HSSFWorkbook();
        }

        // 第二步，在workbook中添加一个sheet,对应Excel文件中的sheet
        HSSFSheet sheet = wb.createSheet(sheetName);

        // 第三步，在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制
        HSSFRow row = sheet.createRow(0);
        row.setHeight((short) 650);
        // 第四步，创建单元格，并设置值表头 设置表头居中
        HSSFCellStyle style = wb.createCellStyle();
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 创建一个居中格式
        style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        //声明列对象
        HSSFCell cell = null;

        //创建标题
        for (int i = 0; i < title.length; i++) {
            sheet.setColumnWidth(i, 6000);
            cell = row.createCell(i);
            cell.setCellValue(title[i]);
            HSSFFont font = wb.createFont();
            font.setFontName("黑体");
            font.setFontHeightInPoints((short) 15);//设置字体大小
            style.setFont(font);
            cell.setCellStyle(style);
        }
//        BufferedImage bufferImg = null;//图片一
//        BufferedImage bufferImg1 = null;//图片二
        try {
            //创建内容
            HSSFCellStyle styleCon = wb.createCellStyle();
            styleCon.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 创建一个居中格式
            styleCon.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
            for (int i = 0; i < list.size(); i++) {
                row = sheet.createRow(i + 1);
                row.setHeight((short) 550);
                BsDepartmentPatientPageVo bsDepartmentPatientPageVo = list.get(i);
                cell = row.createCell(0);
                cell.setCellValue(bsDepartmentPatientPageVo.getHospitalName());
                cell.setCellStyle(styleCon);
                cell = row.createCell(1);
                cell.setCellValue(bsDepartmentPatientPageVo.getDepartmentName());
                cell.setCellStyle(styleCon);
                cell = row.createCell(2);
                cell.setCellValue(bsDepartmentPatientPageVo.getPatientName());
                cell.setCellStyle(styleCon);
                cell = row.createCell(3);
                cell.setCellValue(bsDepartmentPatientPageVo.getGenderValue());
                cell.setCellStyle(styleCon);
                cell = row.createCell(4);
                cell.setCellValue(bsDepartmentPatientPageVo.getIdNumber());
                cell.setCellStyle(styleCon);
                cell = row.createCell(5);
                cell.setCellValue(bsDepartmentPatientPageVo.getWardNumber());
                cell.setCellStyle(styleCon);
                cell = row.createCell(6);
                cell.setCellValue(bsDepartmentPatientPageVo.getBedNumber());
                cell.setCellStyle(styleCon);
                String hospitalizationDate = "";
                if (bsDepartmentPatientPageVo.getHospitalizationDate() != null) {
                    hospitalizationDate = sdf.format(bsDepartmentPatientPageVo.getHospitalizationDate());
                }
                cell = row.createCell(7);
                cell.setCellValue(hospitalizationDate);
                cell.setCellStyle(styleCon);
                String leaveHospitalDate = "";
                if (bsDepartmentPatientPageVo.getLeaveHospitalDate() != null) {
                    leaveHospitalDate = sdf.format(bsDepartmentPatientPageVo.getLeaveHospitalDate());
                }
                cell = row.createCell(8);
                cell.setCellValue(leaveHospitalDate);
                cell.setCellStyle(styleCon);
                cell = row.createCell(9);
                cell.setCellValue(bsDepartmentPatientPageVo.getOtherMessage());
                cell.setCellStyle(styleCon);
                cell = row.createCell(10);
                cell.setCellValue(bsDepartmentPatientPageVo.getRemark());
                cell.setCellStyle(styleCon);
            }
            return wb;
        } catch (Exception e) {
            // TODO: handle exception
            System.err.println(e.getMessage());
        }
        return wb;
    }
//
//    /**
//     * 导出Excel（主推）
//     * @param title 标题
//     * @param wb HSSFWorkbook对象
//     * @return
//     */
//    public static HSSFWorkbook getSeriesHSSFWorkbook(String []title,String []titleSpecimen,List<KeeSeries> list,Map<String, List<KeeSpecimen>> map, HSSFWorkbook wb){
//        HttpServletRequest request = ((ServletRequestAttributes)RequestContextHolder.getRequestAttributes()).getRequest();
//        // 第一步，创建一个HSSFWorkbook，对应一个Excel文件
//        if(wb == null){
//            wb = new HSSFWorkbook();
//        }
//        //样式
//        HSSFCellStyle style = wb.createCellStyle();
//        style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 创建一个居中格式
//        style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
//
//        HSSFCellStyle styleCon = wb.createCellStyle();
//        styleCon.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 创建一个居中格式
//        styleCon.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
//        //字体
//        HSSFFont font = wb.createFont();
//        font.setFontName("黑体");
//        font.setFontHeightInPoints((short) 17);//设置字体大小
//        style.setFont(font);
//        // 第二步，在workbook中添加一个sheet,对应Excel文件中的sheet（根据主推来创建）
//        for (KeeSeries keeSeries : list) {
//            HSSFSheet sheet = wb.createSheet(keeSeries.getChName());
//            HSSFCell cell = null;
//            //设置表头的列宽
//            for (int i = 0; i < title.length; i++) {
//                sheet.setColumnWidth(i, 6000);
//            }
//            // 第三步，在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制
//            //设置主推表头
//            HSSFRow row = sheet.createRow(0);
//            row.setHeight((short) 1000);
//            cell=row.createCell(0);
//            cell.setCellValue("主推系列");
//            cell.setCellStyle(style);
//            //合并第一行，6列
//            CellRangeAddress region=new CellRangeAddress(0, 0, 0,6);
//            sheet.addMergedRegion(region);
//            //设置样品表头
//            HSSFRow rowThree = sheet.createRow(3);
//            rowThree.setHeight((short) 1000);
//            cell=rowThree.createCell(0);
//            cell.setCellValue("系列样品");
//            cell.setCellStyle(style);
//            CellRangeAddress regionT=new CellRangeAddress(3, 3, 0,6);
//            sheet.addMergedRegion(regionT);
//
//            //创建主推标题
//            HSSFRow rowOne = sheet.createRow(1);
//            HSSFRow rowFour = sheet.createRow(4);
//            HSSFCell cellFour = null;
//            for(int i=0;i<title.length;i++){
//                rowOne.setHeight((short) 650);
//                rowFour.setHeight((short) 550);
//                cell = rowOne.createCell(i);
//                cell.setCellValue(title[i]);
//                cellFour = rowFour.createCell(i);
//                cellFour.setCellValue(titleSpecimen[i]);
//                font.setFontHeightInPoints((short) 14);//设置字体大小
//                style.setFont(font);
//                cell.setCellStyle(style);
//                cellFour.setCellStyle(style);
//            }
//
//            BufferedImage bufferImg = null;//图片一
//            BufferedImage bufferImg1 = null;//图片二
//            try {
//                //主推系列数据
//                HSSFRow rowTwo = sheet.createRow(2);
//                rowTwo.setHeight((short) 550);
//                ByteArrayOutputStream byteArrayOut = new ByteArrayOutputStream();
//                ByteArrayOutputStream byteArrayOut1 = new ByteArrayOutputStream();
//                HSSFPatriarch patriarch = sheet.createDrawingPatriarch();
//                //将两张图片读到BufferedImage
//                String chImage = keeSeries.getChImage();
//                chImage = chImage.replaceFirst("/kee", "");
//                chImage = request.getSession().getServletContext().getRealPath(chImage);
//                if (new File(chImage).exists()) {
//                    bufferImg = ImageIO.read(new File(chImage));
//                    ImageIO.write(bufferImg, FileUtils.getFileExtension(keeSeries.getChImage()), byteArrayOut);
//                    //图片一导出到单元格B2中
//                    HSSFClientAnchor anchor = new HSSFClientAnchor(480, 30, 700, 250,
//                            (short) 2, 2, (short) 2, 2);
//                    // 插入图片
//                    patriarch.createPicture(anchor, wb.addPicture(byteArrayOut
//                            .toByteArray(), HSSFWorkbook.PICTURE_TYPE_JPEG));
//                }
//
//                String enImage = keeSeries.getEnImage();
//                enImage = enImage.replaceFirst("/kee", "");
//                enImage = request.getSession().getServletContext().getRealPath(enImage);
//                if (new File(enImage).exists()) {
//                    bufferImg1 = ImageIO.read(new File(enImage));
//                    ImageIO.write(bufferImg1, FileUtils.getFileExtension(keeSeries.getEnImage()), byteArrayOut1);
//                    //图片一导出到单元格B6中
//                    HSSFClientAnchor anchor1 = new HSSFClientAnchor(400, 30, 700, 220,
//                            (short) 3, 2, (short) 3, 2);
//                    patriarch.createPicture(anchor1, wb.addPicture(byteArrayOut1
//                            .toByteArray(), HSSFWorkbook.PICTURE_TYPE_JPEG));
//                }
//                cell=rowTwo.createCell(0);cell.setCellValue(keeSeries.getChName());cell.setCellStyle(styleCon);
//                cell=rowTwo.createCell(1);cell.setCellValue(keeSeries.getEnName());cell.setCellStyle(styleCon);
//                cell=rowTwo.createCell(4);cell.setCellValue(keeSeries.getLookNum());cell.setCellStyle(styleCon);
//                cell=rowTwo.createCell(5);cell.setCellValue(keeSeries.getOrdersNum());cell.setCellStyle(styleCon);
//                cell=rowTwo.createCell(6);cell.setCellStyle(styleCon);cell.setCellValue(DictUtils.getDictLabel(keeSeries.getRes1(), "is_show", ""));cell.setCellStyle(styleCon);
//
//                //查询主推下边的样品
//                List<KeeSpecimen> keeSpecimenList = map.get(keeSeries.getId());
//                if (!keeSpecimenList.isEmpty()) {
//                    for(int i=0;i<keeSpecimenList.size();i++){
//                        row = sheet.createRow(i + 5);
//                        row.setHeight((short) 550);
//                        KeeSpecimen keeSpecimen = keeSpecimenList.get(i);
//                        //将两张图片读到BufferedImage
//                        String qrcode = "/static/images/qrcode/"+keeSpecimen.getRes1();
//                        qrcode = request.getSession().getServletContext().getRealPath(qrcode);
//                        if (new File(qrcode).exists()) {
//                            bufferImg = ImageIO.read(new File(qrcode));
//                            ImageIO.write(bufferImg, "jpg", byteArrayOut);
//                            //图片一导出到单元格B2中
//                            HSSFClientAnchor anchor = new HSSFClientAnchor(480, 30, 700, 250,
//                                    (short) 1, i+5, (short) 1, i+5);
//                            // 插入图片
//                            patriarch.createPicture(anchor, wb.addPicture(byteArrayOut
//                                    .toByteArray(), HSSFWorkbook.PICTURE_TYPE_JPEG));
//                        }
//
//                        String baseImage = keeSpecimen.getBaseImage();
//                        baseImage = baseImage.replaceFirst("/kee", "");
//                        baseImage = request.getSession().getServletContext().getRealPath(baseImage);
//                        if (new File(baseImage).exists()) {
//                            bufferImg1 = ImageIO.read(new File(baseImage));
////FileUtils.getFileExtension获取图片的后缀名，是jpg还是png
//                            ImageIO.write(bufferImg1, FileUtils.getFileExtension(keeSpecimen.getBaseImage()), byteArrayOut1);
//                            //图片一导出到单元格B6中
//                            HSSFClientAnchor anchor1 = new HSSFClientAnchor(400, 30, 700, 220,
//                                    (short) 5, i+5, (short) 5, i+5);
//                            patriarch.createPicture(anchor1, wb.addPicture(byteArrayOut1
//                                    .toByteArray(), HSSFWorkbook.PICTURE_TYPE_JPEG));
//                        }
//                        cell=row.createCell(0);cell.setCellValue(keeSpecimen.getCode());cell.setCellStyle(styleCon);
//                        cell=row.createCell(2);cell.setCellValue(DictUtils.getDictLabel(keeSpecimen.getType(), "specimen_type", ""));cell.setCellStyle(styleCon);
//                        cell=row.createCell(3);cell.setCellValue(keeSpecimen.getChName());cell.setCellStyle(styleCon);
//                        cell=row.createCell(4);cell.setCellValue(keeSpecimen.getEnName());cell.setCellStyle(styleCon);
//                        cell=row.createCell(6);cell.setCellValue(keeSpecimen.getOrdersNum()+" / "+keeSpecimen.getLookNum());cell.setCellStyle(styleCon);
//                    }
//                }
//
//            } catch (Exception e) {
//                System.err.println(e.getMessage());
//            }
//
//        }
//
//        return wb;
//    }
}
